//
//  SqliteTool.m
//  SQLite
//
//  Created by 金色榜样研发组 on 2019/5/28.
//  Copyright © 2019 Golden Bangyang (beijing) Cul Ture Media Investment Co. All rights reserved.
//

#import "SqliteTool.h"

#import <sqlite3.h>
#import <objc/runtime.h>

static NSString *const ModelName = @"SQLITETOOLOFMODELNAME";
static NSString *const DefaultTableName = @"DefaultTableName";
#define sqliteDBPath [[NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"SQLite.sqlite"]

@implementation SqliteTool
{
    sqlite3 *_dbPoint;  //用于保存数据库对象的地址
}
+ (SqliteTool *)shareinstance{
    static SqliteTool *tool = nil;
    static dispatch_once_t oneToken;
    dispatch_once(&oneToken, ^{
        tool=[[SqliteTool alloc] init];
    });
    return tool;
}

//如果系统根据这个文件路径查找的时候有对应文件则直接打开数据库,如果没有则会创建一个相应的数据库
#pragma mark -------------- 打开/创建 数据库 --------------
- (BOOL)createSqliteDB{
    NSString *sqlitePath = sqliteDBPath;
//    NSLog(@"sqlitePath = %@",sqlitePath);
    if (_dbPoint) {
        NSLog(@"数据库已经被打开了，无需再次打开了");
        return YES;
    }
    int result = sqlite3_open([sqlitePath UTF8String], &_dbPoint); //在指定路径下，创建一个数据，并将数据库的地址赋值给_dbPoint
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
        NSLog(@"%@",sqlitePath);
        return YES;
    }else{
        NSLog(@"数据库打开失败");
    }
    return NO;
}
#pragma mark -------------- 关闭数据库 --------------
- (BOOL)closeSqliteDB{
    int result = sqlite3_close(_dbPoint);
    if (result==SQLITE_OK) {
        _dbPoint = nil;
        NSLog(@"数据库关闭成功");
        return YES;
        
    }else{
        NSLog(@"数据库关闭失败");
    }
    return NO;
}
#pragma mark -------------- 删除数据库 --------------
- (BOOL)deleteSqliteDB
{
    NSString *sqlitePath = sqliteDBPath;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    if ([fileManager fileExistsAtPath:sqlitePath]) {
        NSError *error;
        if ([fileManager removeItemAtPath:sqlitePath error:&error]) {
            NSLog(@"数据库删除成功");
            return YES;
        }
        else{
            NSLog(@"数据库删除失败");
            return NO;
        }
    }
    return NO;
}
#pragma mark -------------- 删除数据库中的指定表格 --------------
- (BOOL)deleteSQLiteTable:(Class)modelClass
{
    NSString *dropTableString = [NSString stringWithFormat:@"drop table if exists %@",NSStringFromClass(modelClass)];
    const char *drop_stmt = [dropTableString UTF8String];
    char *errMsg;
    if (sqlite3_exec(_dbPoint, drop_stmt, NULL, NULL, &errMsg) == SQLITE_OK) {
        NSLog(@"删除表格（%@）成功",NSStringFromClass(modelClass));
        return YES;
    }
    else{
        NSLog(@"删除表格（%@）失败",NSStringFromClass(modelClass));
        return NO;
    }
}

/*
 创建一个存储列表。 一个数据库可以创建很多列表，用来存储不同的对象。
 
 primary key 是主键的意思,主健在当前表里数据是唯一的,不能重复,可以唯一标识一条数据,一般是整数
 
 autoincrement自增,为了让主键不重复,会让主键采用自增的方式
 
 if not exists 如果没有表才会创建,防止重复创建覆盖之前数据
 
 */
//#pragma mark -------------- 创建表 --------------
//- (BOOL)createTable{
//    //语句中包涵的信息应该仔细， NSString类型对应的是text 数组对应arr 整型对应integer 等等
//    NSString *sqlStr = @"create table if not exists test(number integer primary key autoincrement,name text,age integer,sex text)";
//    //执行这条sql语句
//    int result = sqlite3_exec(_dbPoint, [sqlStr UTF8String], nil, nil, nil);
//
//    if (result == SQLITE_OK) {
//        NSLog(@"表创建成功");
//        return YES;
//    }else{
//        NSLog(@"表创建失败");
//    }
//    return NO;
//}

#pragma mark -------------- 往表中插入数据(当表不存在时，会先创建表) --------------
- (void)insertModel:(id)model{
    
    id object = [self getModel:model];
    if ([object isKindOfClass:[NSArray class]]) {
        NSArray *objectArr = (NSArray *)object;
        for (NSInteger i = 0; i < objectArr.count; ++i) {
            id subObject = objectArr[i];
            if ([subObject isKindOfClass:[NSArray class]]) {
                [self insertModel:subObject];
            }
            else if([subObject isKindOfClass:[NSDictionary class]])
            {
                NSDictionary *dic = (NSDictionary *)subObject;
                [self insertSQLiteWithDic:dic];
            }
        }
    }
    else if ([object isKindOfClass:[NSDictionary class]])
    {
        NSDictionary *dic = (NSDictionary *)object;
        [self insertSQLiteWithDic:dic];
    }
}
#pragma mark - 将字典插入数据库
-(void)insertSQLiteWithDic:(NSDictionary *)dic
{
    NSDictionary *objectDic = [NSDictionary dictionaryWithDictionary:dic];
    NSMutableString *createTableSubString = [NSMutableString new];
    for (NSString *key in [objectDic allKeys]) {
        [createTableSubString appendFormat:@"%@ text,",key];
    }
    if (createTableSubString.length > 0) {
        [createTableSubString deleteCharactersInRange:NSMakeRange(createTableSubString.length - 1, 1)];
    }
    NSString *modelName = [NSString stringWithFormat:@"%@",objectDic[ModelName]];
    NSString *tableName = [self isEmpty:modelName] ? modelName : DefaultTableName;
    NSString *createTableString = [NSString stringWithFormat:@"create table if not exists %@(%@)",tableName,createTableSubString];
    //执行这条sql语句
    int createTableResult = sqlite3_exec(_dbPoint, [createTableString UTF8String], nil, nil, nil);
    if (createTableResult == SQLITE_OK) {
        NSLog(@"表创建成功");
    }else{
        NSLog(@"表创建失败");
    }
    NSMutableString *keyString = [NSMutableString new];
    NSMutableString *valueString = [NSMutableString new];
    for (NSString *key in [objectDic allKeys]) {
        [keyString appendFormat:@"%@,",key];
        [valueString appendFormat:@"'%@',",objectDic[key]];
    }
    
    if (keyString.length > 0) {
        [keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
    }
    if (valueString.length > 0) {
        [valueString deleteCharactersInRange:NSMakeRange(valueString.length - 1,1)];
    }
    NSString *insertTableSqlStr=[NSString stringWithFormat:@"insert into %@ (%@) values (%@)",tableName,keyString,valueString];
    //执行sql语句
    int insertTableSqlResult = sqlite3_exec(_dbPoint, [insertTableSqlStr UTF8String], nil, nil, nil);
    if (insertTableSqlResult == SQLITE_OK) {
        NSLog(@"添加model成功");
    }else {
        NSLog(@"添加model失败");
    }
}

#pragma mark -------------- 删除表中的内容 --------------
- (BOOL)deleteModelClass:(Class)modelClass withKey:(NSString *)key value:(NSString *)value{
        NSString *deleteSqlString= [NSString stringWithFormat:@"delete from %@ where %@='%@'",NSStringFromClass(modelClass),key,value];
        //执行sql语句
        int deleteResult = sqlite3_exec(_dbPoint, [deleteSqlString UTF8String], nil, nil, nil);
        if (deleteResult == SQLITE_OK) {
            NSLog(@"删除成功");
            return YES;
        }else {
            NSLog(@"删除失败");
            return NO;
        }
}

- (BOOL)deleteModel:(id)model
{
    id object = [self getModel:model];
    if ([object isKindOfClass:[NSDictionary class]]) {
        NSMutableString *sqliteSubString = [NSMutableString new];
        NSDictionary *objectDic = (NSDictionary *)object;
        for (NSString *key in [objectDic allKeys]) {
            [sqliteSubString appendFormat:@"%@='%@'and ",key,objectDic[key]];
        }
        if (sqliteSubString.length > 4) {
            [sqliteSubString deleteCharactersInRange:NSMakeRange(sqliteSubString.length - 4, 4)];
        }
        NSString *modelName = [NSString stringWithFormat:@"%@",objectDic[ModelName]];
        NSString *tableName = [self isEmpty:modelName] ? modelName : DefaultTableName;
        NSString *deleteSqlString= [NSString stringWithFormat:@"delete from %@ where %@",tableName,sqliteSubString];
        //执行sql语句
        int deleteResult = sqlite3_exec(_dbPoint, [deleteSqlString UTF8String], nil, nil, nil);
        if (deleteResult == SQLITE_OK) {
            NSLog(@"删除成功");
            return YES;
        }else {
            NSLog(@"删除失败");
        }
    }
    return NO;
}

#pragma mark -------------- 更新表中的数据 --------------
- (BOOL)updateModel:(id)model withKey:(NSString *)key value:(NSString *)value{
    id object = [self getModel:model];
    if ([object isKindOfClass:[NSDictionary class]]) {
        NSDictionary *objectDic = (NSDictionary *)object;
        NSMutableString *updateSqlSubString = [NSMutableString new];
        for (NSString *key in [objectDic allKeys]) {
            [updateSqlSubString appendFormat:@"%@='%@',",key,objectDic[key]];
        }
        if (updateSqlSubString.length > 0) {
            [updateSqlSubString deleteCharactersInRange:NSMakeRange(updateSqlSubString.length - 1, 1)];
        }
        NSString *modelName = [NSString stringWithFormat:@"%@",objectDic[ModelName]];
        NSString *tableName = [self isEmpty:modelName] ? modelName : DefaultTableName;
        NSString *updateSqlString= [NSString stringWithFormat:@"update %@ set %@ where %@='%@'",tableName,updateSqlSubString,key,value];
        //执行sql语句
        int updateSqlResult = sqlite3_exec(_dbPoint, [updateSqlString UTF8String], nil, nil, nil);
        if (updateSqlResult == SQLITE_OK) {
            NSLog(@"修改成功");
            return YES;
        }else {
            NSLog(@"修改失败");
            return NO;
        }
    }
    return NO;
}

/*
 查询逻辑
 1.先从本地的数据库中读取某张表里的所有数据
 2.然后逐条进行读取,对model进行赋值
 3.把已经赋值好得model放到数组中,并且返回
 4.在语句里*是通配符的意思,通过一个*相当于代替了表里的所有的字段名
 5.接下来需要定义一个跟随指针,它用来遍历数据库表中的每行数据
 6.第三个参数:查询语句字数限制,-1是没有限制
 */

#pragma mark -------------- 查看表中的数据 --------------
- (NSArray *)selectAllModel:(Class)modelClass{
    NSString *sqlStr=[NSString stringWithFormat:@"select * from %@",NSStringFromClass(modelClass)];
    sqlite3_stmt *stmt=nil;
    int result=sqlite3_prepare_v2(_dbPoint, [sqlStr UTF8String], -1, &stmt, nil);//这个方法相当于把数据库和跟随指针关联,一同完成查询功能
    
    //初始化学生类数组 获取遍历得到的数据
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询数据库的每一行数据
        //定义一个存放数据字典的可变数组
        NSMutableArray *modelArr = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //一共获取表中所有列数(字段数)
            int columnCount = sqlite3_column_count(stmt);
            //定义存放字段数据的模型
            
            id model = [[modelClass alloc] init];
            
            for (int i = 0; i < columnCount; i++)
            { // 取出i位置列的字段名,作为字典的键key
                const char *cKey = sqlite3_column_name(stmt, i);
                NSString *key = [NSString stringWithUTF8String:cKey];
                //取出i位置存储的值,作为字典的值value
                const char *cValue = (const char *)sqlite3_column_text(stmt, i);
                NSString *value = [NSString stringWithUTF8String:cValue];
                //将此行数据 中此字段中key和value包装成 模型
                if (![key isEqualToString:ModelName]) {
                    [model setValue:value forKey:key];
                }
            }
            [modelArr addObject:model];
            
        }
        //7 释放跟随指针占用的内存
        sqlite3_finalize(stmt);
        return [NSArray arrayWithArray:modelArr];
    }else{
        NSLog(@"查询失败");
    }
    //7 释放跟随指针占用的内存
    sqlite3_finalize(stmt);
    return [NSArray new];
}

#pragma mark -------------- 查询表中的数据 --------------
- (NSArray *)selectModel:(Class)modelClass withKey:(NSString *)key value:(NSString *)value{
    NSString *sqlStr=[NSString stringWithFormat:@"select * from %@ where %@='%@'",NSStringFromClass(modelClass),key,value];
    sqlite3_stmt *stmt=nil;
    int result=sqlite3_prepare_v2(_dbPoint, [sqlStr UTF8String], -1, &stmt, nil);//这个方法相当于把数据库和跟随指针关联,一同完成查询功能
    
    //初始化学生类数组 获取遍历得到的数据
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询数据库的每一行数据
        //定义一个存放数据字典的可变数组
        NSMutableArray *modelArr = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //一共获取表中所有列数(字段数)
            int columnCount = sqlite3_column_count(stmt);
            //定义存放字段数据的模型
            
            id model = [[modelClass alloc] init];
            
            for (int i = 0; i < columnCount; i++)
            { // 取出i位置列的字段名,作为字典的键key
                const char *cKey = sqlite3_column_name(stmt, i);
                NSString *key = [NSString stringWithUTF8String:cKey];
                //取出i位置存储的值,作为字典的值value
                const char *cValue = (const char *)sqlite3_column_text(stmt, i);
                NSString *value = [NSString stringWithUTF8String:cValue];
                //将此行数据 中此字段中key和value包装成 模型
                if (![key isEqualToString:ModelName]) {
                    [model setValue:value forKey:key];
                }
            }
            [modelArr addObject:model];
            
        }
        //7 释放跟随指针占用的内存
        sqlite3_finalize(stmt);
        return [NSArray arrayWithArray:modelArr];
    }else{
        NSLog(@"查询失败");
    }
    //7 释放跟随指针占用的内存
    sqlite3_finalize(stmt);
    return [NSArray new];
}



-(id)getModel:(id)model
{
    if ([model isKindOfClass:[NSArray class]]) {
        NSMutableArray *array = [NSMutableArray new];
        NSArray *modelArr = [NSArray arrayWithArray:(NSArray *)model];
        for (NSInteger i = 0; i < modelArr.count; ++i) {
            id object = modelArr[i];
            if ([object isKindOfClass:[NSArray class]]) {
                [self getModel:object];
            } else {
                NSDictionary *objectDic = [self getModelValueAndKeys:object];
                [array addObject:objectDic];
            }
        }
        return array;
    }
    else{
        NSDictionary *objectDic = [self getModelValueAndKeys:model];
        return objectDic;
    }
}

-(NSDictionary *)getModelValueAndKeys:(id)model
{
    if (!model) {
        NSLog(@"传入的对象为nil");
        return [NSDictionary new];
    }
    NSMutableDictionary *objectDic = [NSMutableDictionary new];
    if ([model isKindOfClass:[NSDictionary class]]) {
        [objectDic setObject:NSStringFromClass([NSDictionary class]) forKey:ModelName];
        return model;
    }
    unsigned int count;
    Class class = [model class];
    objc_property_t *properties = class_copyPropertyList(class, &count);
    [objectDic setObject:NSStringFromClass(class) forKey:ModelName];
    for (NSInteger i = 0; i < count; ++i) {
        objc_property_t property = properties[i];
        const char *cName = property_getName(property);
        NSString *propertyName = [NSString stringWithCString:cName encoding:NSUTF8StringEncoding];
        id propertyValue = [model valueForKey:propertyName];
        if (propertyValue) {
            [objectDic setObject:propertyValue forKey:propertyName];
        }
    }
    return [NSDictionary dictionaryWithDictionary:objectDic];
}
#pragma mark - 判断字符串是否为空
-(BOOL)isEmpty:(NSString *)text
{
    if (!text || [[text lowercaseString] isEqualToString:@"null"] || [[text lowercaseString] isEqualToString:@"(null)"]) {
        return NO;
    }
    return YES;
}
@end
